create database LicensePlate
go

use licenseplate
go

create table users (
	id int identity(1, 1) primary key not null,
	user_name nvarchar(50) not null,
	password char(40) not null,
	full_name nvarchar(100) not null default(''),
	email nvarchar(100) not null default(''),
	mobile_phone varchar(20) not null default(''),
	flag tinyint not null default(0)
)
go

create proc p_users_init
	@id int output
as
begin
	if exists (select * from users where user_name = 'admin')
	begin
		set @id = -1
		return @id
	end

	insert into users (
		user_name,
		password,
		full_name)
	values (
		'admin',
		convert(char(40), hashbytes('sha', 'admin'), 2),
		'admin')

	if @@ROWCOUNT = 1
	begin
		set @id = @@IDENTITY
		return @id
	end
	else
	begin
		set @id = -2
		return @id
	end
end
go

create proc [dbo].[p_users_admin_reset]
	@password nvarchar(50),
	@id int output
as
begin
	if exists (select * from users where user_name='admin')
	begin
		update users set
			password=convert(char(40), HASHBYTES('sha',@password),2)
		where
			user_name='admin'

		if @@ROWCOUNT != 1
		begin
			set @id = -1
			return @id
		end
		
		select @id = id from users where user_name='admin'
		return @id
	end
	else
	begin
		set @id = -2
		return @id
	end
end
go

create proc p_users_i
	@user_name nvarchar(50),
	@password nvarchar(50),
	@full_name nvarchar(100) = '',
	@email nvarchar(100),
	@mobile_phone varchar(20),
	@id int output
as
begin
	if exists (select * from users where user_name = @user_name)
	begin
		set @id = -1
		return @id
	end

	set @password = ltrim(rtrim(@password))

	if @password = ''
	begin
		set @id = -2
		return @id
	end

	insert into users (
		user_name,
		password,
		full_name,
		email,
		mobile_phone)
	values (
		@user_name,
		convert(char(40), HASHBYTES('sha',@password), 2),
		@full_name,
		@email,
		@mobile_phone
	)

	if @@ROWCOUNT = 1
	begin
		set @id = @@IDENTITY
		return @id
	end
	else
	begin
		set @id = -3
		return @id
	end
end
go

create proc p_users_u
	@id int output,
	@user_name nvarchar(50),
	@password nvarchar(50),
	@full_name nvarchar(100) = '',
	@email nvarchar(100) = '',
	@mobile_phone varchar(20) = ''
as
begin
	if not exists (select * from users where id = @id)
	begin
		set @id = -1
		return @id
	end

	set @user_name = ltrim(rtrim(@user_name))
	if @user_name = ''
	begin
		set @id = -2
		return @id
	end

	if exists (select * from users where user_name = @user_name and id != @id)
	begin
		set @id = -3
		return @id
	end

	set @password = ltrim(rtrim(@password))
	if @password = ''
	begin
		set @id = -4
		return @id
	end

	update users set
		user_name = @user_name,
		password = convert(char(40), hashbytes('sha', @password), 2),
		full_name = @full_name,
		email = @email,
		mobile_phone = @mobile_phone
	where
		id = @id

	if @@ROWCOUNT = 1
	begin
		return @id
	end
	else
	begin
		set @id = -5
		return @id
	end
end
go

create proc p_users_active
	@id int output
as
begin
	if not exists (select * from users where id = @id)
	begin
		set @id = -1
		return @id
	end

	update users set
		flag = 1
	where
		id = @id

	if @@ROWCOUNT =1
	begin
		return @id
	end

	set @id = -2
	return @id

end
go

create proc p_users_close
	@id int output
as
begin
	if not exists (select * from users where id = @id)
	begin
		set @id = -1
		return @id
	end

	update users set
		flag = 2
	where
		id = @id

	if @@ROWCOUNT = 1
	begin
		return @id
	end
	begin
		set @id = -2
		return @id
	end
end
go

create proc p_users_login
	@user_name nvarchar(50),
	@password nvarchar(50),
	@id int output
as
begin
	if not exists (select * from users where user_name=@user_name)
	begin
		set @id =-1
		return @id
	end

	set @password=TRIM(@password)
	if @password = ''
	begin
		set @id = -2
		return @id
	end
	select @id = id from users where user_name=@user_name
	
	if exists (select * from users where id=@id and password = CONVERT(char(40), HASHBYTES('sha',@password),2))
	begin
		return @id
	end
	else
	begin
		set @id = -3
		return @id
	end


end
go